How to Change the BuddyPress User Query with SQL

Sometimes you might need to change the member’s list in BuddyPress to display them by some other criteria. That might be something that can’t be done through classic filters and WordPress API. In this tutorial, I’ll show you how to change the SQL for the BuddyPress User Query to show only members with membership from MemberPress.

In the previous article on filtering the BuddyPress Member List, I have shown you how to Display Members in BuddyPress by Roles. This article will now show you how to use SQL to do similar filtering.

BuddyPress User Query

The BuddyPress User Query can be found in the class BP_User_Query. If you spend enough time reading the code as I was, you’ll find a filter that allows us to change the query through SQL.

That filter is bp_user_query_uid_clauses and what there are two parameters that are passed in that filter:

• $sql – array with SQL statements. Array keys are: select, where, orderby, order & limit.
• $query – this is the actual BP_User_Query object on which the SQL is performed.

Even BuddyPress is using that filter to change the SQL with XProfile Fields. Feel free to search the BuddyPress plugin for the function bp_xprofile_bp_user_query_search to see how they did it.

SQL Statements

Of course, it could be useful to know what are we actually getting in that $sql array, right? If you go and search for the method prepare_user_ids_query inside the BP_User_Query you would see a lot of those.

Based on the type of our member list which can be changed through the member list filter, we have different queries. Here are the types:

• online – table: bp_activity
• active, newest, random – table: bp_activity
• popular – table: usermeta
• alphabetical – if xProfile is not active table: users, but if it’s active then table:bp_xprofile_data
• other types – table: users

I have also written on which tables the query is performed based on the types. The where clause is done differently. The $sql is a multidimensional array thus each item is another array that holds where clauses. Each clause should begin with AND something=something.

Other than that, all others are simple strings, for example: $sql = "ORDER BY u.value"; and $sql = "ORDER BY u.value"; $sql   = "ASC";.

Filtering only Active MemberPress Members

We will now filter the $sql array so that we can retrieve only users who have an active membership in MemberPress.

To do that, we need to JOIN the table from MemberPress where such info is saved. Once that is done, we must also write a new WHERE clause that will exclude any users who do not have any active memberships.

Here is how it’s done:

First, we check if MemberPress is active by checking if their class exists. After that, we are writing a JOIN statement so that we can add the MemberPress table to our query.

We are also writing a new WHERE clause where we require only users who have an active membership.

Conclusion

The WordPress Plugin API was helpful again. And that is only because BuddyPress developers have made the plugin really extensible. To filter the BuddyPress User Query with SQL, you should know a little of SQL to begin with. Be sure to create good and non-vulnerable queries.

Have you ever creating custom member lists in BuddyPress? How did you extend the BuddyPress plugin?

If you want to up your WordPress development knowledge, I have a course, you might like. Building Themes, Plugins, optimizing them & selling is something you might want to know? Check it out: Become a WordPress Developer.

Become a Sponsor